﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TpBaseObjects.Properties;
using Microsoft.Office.Interop.Excel;
using TpLogger;
using TpExtensions;

namespace TpBaseObjects.Base {
    public class ProjectSheet {

        #region Members

        private Worksheet mySheet;
        private List<ProjectRow> myRows = new List<ProjectRow>();
        public delegate int GetFreeNumber(string bereich, Worksheet sheet, Logger log);
        private List<DateTime> lieferscheinDates = new List<DateTime>();
        private List<DateTime> abnahmeProtokollDates = new List<DateTime>();
        private List<DateTime> durchfuehrungsDates = new List<DateTime>();
        private DateTime auftragsBestaetigungsDatum = new DateTime(1900, 1, 1);
        private bool reparatur = false;

        #endregion

        #region Contructor

        public ProjectSheet(Worksheet sh) {
            mySheet = sh;
            SetRowNumbers();
            LoadDurchführungsDates();
            LoadAuftragsDates();
            LoadLieferscheinDates();
            LoadAbnahmeprotokollDates();
            LoadAbnahmeprotokollDatesRep();
            LoadLieferscheinDatesRep();
            LoadCheckReparatur();

        }

        public ProjectSheet(Worksheet sh, bool addRows)
            : this(sh) {
            int startRow = 3;
            if (addRows) {
                if (IsCalcSheet) {
                    startRow = Settings.Default.ZeileStart;
                }
                this.AddAllSheetRows(startRow, IsCalcSheet, false, false);
            }
        }

        public ProjectSheet(Worksheet sh, bool onlyWithJobDate, bool intern)
            : this(sh) {
            int startRow = 3;

            if (IsCalcSheet) {
                startRow = Settings.Default.ZeileStart;
            }
            this.AddAllSheetRows(startRow, IsCalcSheet, intern, onlyWithJobDate);

        }

        #endregion

        #region Properties

        #region AepüWerte

        #region Erlös

        public double AeErlösMinderung {
            get {
                if (ExistsName("ErlösMinderung")) {
                    Range r1 = mySheet.Range["ErlösMinderung"];
                    return Extensions.Nz(r1.Value, 0);
                }
                return 0;
            }
        }

        public double AeForderungsausfall {
            get {
                if (ExistsName("ErlösForderungsausfall")) {
                    Range r1 = mySheet.Range["ErlösForderungsausfall"];
                    return Extensions.Nz(r1.Value, 0);
                }
                return 0;
            }
        }

        public double AeRabattFix {
            get {
                if (ExistsName("ErlösRabattFix")) {
                    Range r1 = mySheet.Range["ErlösRabattFix"];
                    return Math.Abs(Extensions.Nz(r1.Value, 0));
                }
                return 0;
            }
        }

        public double AeRabattLeistungenFix {
            get {
                if (ExistsName("ErlösRabattLeistungenFix")) {
                    Range r1 = mySheet.Range["ErlösRabattLeistungenFix"];
                    return Math.Abs(Extensions.Nz(r1.Value, 0));
                }
                return 0;
            }
        }

        public double AeRabattLeistungenProzent {
            get {
                if (ExistsName("ErlösRabattLeistungenProzent")) {
                    Range r1 = mySheet.Range["ErlösRabattLeistungenProzent"];
                    return Math.Abs(Extensions.Nz(r1.Value, 0));
                }
                return 0;
            }
        }

        public double AeRabattProdukteFix {
            get {
                if (ExistsName("ErlösRabattProdukteFix")) {
                    Range r1 = mySheet.Range["ErlösRabattProdukteFix"];
                    return Math.Abs(Extensions.Nz(r1.Value, 0));
                }
                return 0;
            }
        }

        public double AeRabattProdukteProzent {
            get {
                if (ExistsName("ErlösRabattProdukteProzent")) {
                    Range r1 = mySheet.Range["ErlösRabattProdukteProzent"];
                    return Math.Abs(Extensions.Nz(r1.Value, 0));
                }
                return 0;
            }
        }

        public double AeRabattProzent {
            get {
                if (ExistsName("ErlösRabattProzent")) {
                    Range r1 = mySheet.Range["ErlösRabattProzent"];
                    return Math.Abs(Extensions.Nz(r1.Value, 0));
                }
                return 0;
            }
        }

        public double AeSchadenfall {
            get {
                if (ExistsName("ErlösSchadenfall")) {
                    Range r1 = mySheet.Range["ErlösSchadenfall"];
                    return Math.Abs(Extensions.Nz(r1.Value, 0));
                }
                return 0;
            }
        }

        public double AeSummeHandelsware {
            get {
                if (ExistsName("ErlösSummeHandelsware")) {
                    Range r1 = mySheet.Range["ErlösSummeHandelsware"];
                    return Extensions.Nz(r1.Value, 0);
                }
                return 0;
            }
        }

        public double AeSummeProduktion {
            get {
                if (ExistsName("ErlösSummeProduktion")) {
                    Range r1 = mySheet.Range["ErlösSummeProduktion"];
                    return Extensions.Nz(r1.Value, 0);
                }
                return 0;
            }
        }

        public double AeSummeMontage {
            get {
                if (ExistsName("ErlösSummeMontage")) {
                    Range r1 = mySheet.Range["ErlösSummeMontage"];
                    return Extensions.Nz(r1.Value, 0);
                }
                return 0;
            }
        }

        public double AeZusatzRabattProzent {
            get {
                if (ExistsName("ErlösZusatzRabattProzent")) {
                    Range r1 = mySheet.Range["ErlösZusatzRabattProzent"];
                    return Math.Abs(Extensions.Nz(r1.Value, 0));
                }
                return 0;
            }
        }

        #endregion

        #region Kosten

        public double AeKostenSummeHandelsware {
            get {
                if (ExistsName("KostenSummeHandelsware")) {
                    Range r1 = mySheet.Range["KostenSummeHandelsware"];
                    return Extensions.Nz(r1.Value, 0);
                }
                return 0;
            }
        }

        public double AeKostenSummeProduktion {
            get {
                if (ExistsName("KostenSummeProduktion")) {
                    Range r1 = mySheet.Range["KostenSummeProduktion"];
                    return Extensions.Nz(r1.Value, 0);
                }
                return 0;
            }
        }

        public double AeKostenSummeMontage {
            get {
                if (ExistsName("KostenSummeMontage")) {
                    Range r1 = mySheet.Range["KostenSummeMontage"];
                    return Extensions.Nz(r1.Value, 0);
                }
                return 0;
            }
        }

        public double AeKostenSummeAdministration {
            get {
                if (ExistsName("KostenSummeAdministration")) {
                    Range r1 = mySheet.Range["KostenSummeAdministration"];
                    return Extensions.Nz(r1.Value, 0);
                }
                return 0;
            }
        }

        #endregion

        #region Zusatzkosten

        public double AeZusatzKostenBaustelleninfrastrukturProzent {
            get {
                if (ExistsName("ZusatzKostenBaustelleninfrastrukturProzent")) {
                    Range r1 = mySheet.Range["ZusatzKostenBaustelleninfrastrukturProzent"];
                    return Math.Abs(Extensions.Nz(r1.Value, 0));
                }
                return 0;
            }
        }

        public double AeZusatzKostenBaustelleninfrastrukturFix {
            get {
                if (ExistsName("ZusatzKostenBaustelleninfrastrukturFix")) {
                    Range r1 = mySheet.Range["ZusatzKostenBaustelleninfrastrukturFix"];
                    return Math.Abs(Extensions.Nz(r1.Value, 0));
                }
                return 0;
            }
        }

        public double AeZusatzKostenProvisionsErlösFix {
            get {
                if (ExistsName("ZusatzKostenProvisionsErlösFix")) {
                    Range r1 = mySheet.Range["ZusatzKostenProvisionsErlösFix"];
                    return Math.Abs(Extensions.Nz(r1.Value, 0));
                }
                return 0;
            }
        }

        public double AeZusatzKostenProvisionsErlösProzent {
            get {
                if (ExistsName("ZusatzKostenProvisionsErlösProzent")) {
                    Range r1 = mySheet.Range["ZusatzKostenProvisionsErlösProzent"];
                    return Math.Abs(Extensions.Nz(r1.Value, 0));
                }
                return 0;
            }
        }

        public double AeZusatzkostenErfüllungsgarantie {
            get {
                if (ExistsName("ZusatzkostenErfüllungsgarantie")) {
                    Range r1 = mySheet.Range["ZusatzkostenErfüllungsgarantie"];
                    return Math.Abs(Extensions.Nz(r1.Value, 0));
                }
                return 0;
            }
        }

        public double AeZusatzkostenHaftrücklass {
            get {
                if (ExistsName("ZusatzkostenHaftrücklass")) {
                    Range r1 = mySheet.Range["ZusatzkostenHaftrücklass"];
                    return Math.Abs(Extensions.Nz(r1.Value, 0));
                }
                return 0;
            }
        }

        public double AeZusatzkostenProvsionDB {
            get {
                if (ExistsName("ZusatzkostenProvsionDB")) {
                    Range r1 = mySheet.Range["ZusatzkostenProvsionDB"];
                    return Math.Abs(Extensions.Nz(r1.Value, 0));
                }
                return 0;
            }
        }

        public double AeZusatzkostenSkonto {
            get {
                if (ExistsName("ZusatzkostenSkonto")) {
                    Range r1 = mySheet.Range["ZusatzkostenSkonto"];
                    return Math.Abs(Extensions.Nz(r1.Value, 0));
                }
                return 0;
            }
        }

        #endregion

        #region Zeitaufwand

        public double AeZeitaufwandAdminsitration {
            get {
                if (ExistsName("ZeitaufwandAdminsitration")) {
                    Range r1 = mySheet.Range["ZeitaufwandAdminsitration"];
                    return Extensions.Nz(r1.Value, 0);
                }
                return 0;
            }
        }

        public double AeZeitaufwandMontage {
            get {
                if (ExistsName("ZeitaufwandMontage")) {
                    Range r1 = mySheet.Range["ZeitaufwandMontage"];
                    return Extensions.Nz(r1.Value, 0);
                }
                return 0;
            }
        }

        public double AeZeitaufwandProdukte {
            get {
                if (ExistsName("ZeitaufwandProdukte")) {
                    Range r1 = mySheet.Range["ZeitaufwandProdukte"];
                    return Extensions.Nz(r1.Value, 0);
                }
                return 0;
            }
        }

        public double AeZeitaufwandProdutktion {
            get {
                if (ExistsName("ZeitaufwandProdutktion")) {
                    Range r1 = mySheet.Range["ZeitaufwandProdutktion"];
                    return Extensions.Nz(r1.Value, 0);
                }
                return 0;
            }
        }

        #endregion

        #endregion

        public double Akonto {
            get {
                double ret = 0.0;
                if (!ExistsName("Akonto1")) {
                    Range r1 = mySheet.Columns["AF"];
                    Range aCell = mySheet.Cells[301, "AF"];
                    object toFind = "Akonto";
                    int row = GetNextRowNumber(mySheet, r1, aCell, toFind, true);
                    if (row > 0) {
                        Range rDat = mySheet.Cells[row, "AH"];
                        if (rDat != null) {
                            string d1 = Convert.ToString(rDat.Value);
                            if (d1.Contains("1900")) {
                                return 0.0;
                            }
                            Range retAkonto = mySheet.Cells[row, "AR"];
                            return retAkonto.Value;
                        }
                        return 0.0;
                    }
                    return 0.0;
                }

                Range t1 = mySheet.Range["Akonto1"];
                if (t1.Value == null) {
                    Range rDat = mySheet.Cells[t1.Row, "AH"];
                    string d1 = Convert.ToString(rDat.Value);
                    if (d1.Contains("1900")) {
                        return 0.0;
                    }
                    ret = t1.Value;
                }

                if (ExistsName("Akonto2")) {
                    Range rDat = mySheet.Cells[t1.Row, "AH"];
                    string d1 = Convert.ToString(rDat.Value);
                    if (d1.Contains("1900")) {
                        return 0.0;
                    }
                    Range a2 = mySheet.Range["Akonto2"];
                    ret += a2.Value;
                }
                if (ExistsName("Akonto3")) {
                    Range rDat = mySheet.Cells[t1.Row, "AH"];
                    string d1 = Convert.ToString(rDat.Value);
                    if (d1.Contains("1900")) {
                        return 0.0;
                    }
                    Range a3 = mySheet.Range["Akonto3"];
                    ret += a3.Value;
                }
                return t1.Value;
            }
        }

        public DateTime AkontoDatum {
            get {
                DateTime ret = new DateTime(1900, 1, 1);
                if (!ExistsName("Akonto")) {
                    Range r1 = mySheet.Columns["AF"];
                    Range aCell = mySheet.Cells[301, "AF"];
                    object toFind = "Akonto";
                    int row = GetNextRowNumber(mySheet, r1, aCell, toFind, true);
                    if (row > 0) {
                        Range retBrutto = mySheet.Cells[row, "AH"];
                        return retBrutto.Value;
                    }
                    return ret;
                }
                Range t1 = mySheet.Range["Akonto"];
                Range t2 = mySheet.Cells[t1.Row, "AH"];
                if (t2.Value == null) {
                    return ret;
                }
                return t2.Value;
            }
        }

        public string AkontoBeleg {
            get {
                string ret = "";
                if (!ExistsName("Akonto")) {
                    return ret;
                }
                Range t1 = mySheet.Range["Akonto"];
                Range t2 = mySheet.Cells[t1.Row, "AO"];
                if (t2.Value == null) {
                    return ret;
                }
                return t2.Value;
            }
        }

        public DateTime ApDate1 {
            get {
                DateTime ret = new DateTime(1900, 1, 1);
                if (abnahmeProtokollDates.Count > 0) {
                    abnahmeProtokollDates.Sort();
                    int anz = abnahmeProtokollDates.Count;
                    int row = (anz < 3 ? anz : 3);
                    ret = abnahmeProtokollDates[0 + anz - row];
                }
                return ret;
            }
        }

        public DateTime ApDate2 {
            get {
                DateTime ret = new DateTime(1900, 1, 1);
                if (abnahmeProtokollDates.Count > 1) {
                    abnahmeProtokollDates.Sort();
                    int anz = abnahmeProtokollDates.Count;
                    int row = (anz < 3 ? anz : 3);
                    ret = abnahmeProtokollDates[1 + anz - row];
                }
                return ret;
            }
        }

        public DateTime ApDate3 {
            get {
                DateTime ret = new DateTime(1900, 1, 1);
                if (abnahmeProtokollDates.Count > 2) {
                    abnahmeProtokollDates.Sort();
                    int anz = abnahmeProtokollDates.Count;
                    int row = (anz < 3 ? anz : 3);
                    ret = abnahmeProtokollDates[2 + anz - row];
                }
                return ret;
            }
        }

        public DateTime AuftragsDatum {
            get {
                Range r1 = mySheet.Range[Settings.Default.ZelleAuftragsDatum];
                if ((r1.Value.ToString().Contains("Auf")) | (r1.Value == null)) {
                    return new DateTime(1900, 1, 1);
                }
                return Extensions.Nz(r1.Value, new DateTime(1900, 1, 1));
            }
            set {
                Range r1 = mySheet.Range[Settings.Default.ZelleAuftragsDatum];
                r1.Value = value;
            }
        }

        public DateTime AuftragsBestaetigung {
            get {
                return auftragsBestaetigungsDatum;
            }
        }

        public int AnzahlZeilen {
            get { return myRows.Count; }
        }

        public string CalcCharacter {
            get {
                if (!Extensions.isNumeric(mySheet.Name.Right(1), System.Globalization.NumberStyles.Any)) {
                    if (CalcType == "R") {
                        return "a";
                    }
                    return mySheet.Name.Right(1);
                }
                return "";
            }
        }

        public int CalcNumber {
            get {
                int calcNr = 0;
                if (CalcType == "K") {
                    calcNr = Convert.ToInt32(Extensions.Mid(SheetName.ToString(), SheetName.IndexOf("k") + 1));
                }
                if ((CalcType == "KA") | (CalcType == "R")) {
                    calcNr = 1;
                }
                return calcNr;
            }
        }

        public string CalcType {
            get {
                if (mySheet.Name.Contains("k") & !Extensions.isNumeric(mySheet.Name.Right(1), System.Globalization.NumberStyles.Any)) {
                    return "KA";
                }
                if (mySheet.Name.Contains("r")) {
                    return "R";
                }
                if (mySheet.Name.Contains("k") & Extensions.isNumeric(mySheet.Name.Right(1), System.Globalization.NumberStyles.Any)) {
                    return "K";
                }
                return "";
            }
        }

        public DateTime DurchfuehrungVon {
            get {
                DateTime ret = new DateTime(1900, 1, 1);
                if (durchfuehrungsDates.Count > 0) {
                    durchfuehrungsDates.Sort();
                    ret = durchfuehrungsDates[0];
                }
                return ret;
            }
        }

        public DateTime DurchfuehrungBis {
            get {
                DateTime ret = new DateTime(1900, 1, 1);
                if (durchfuehrungsDates.Count > 0) {
                    durchfuehrungsDates.Sort();
                    int anz = durchfuehrungsDates.Count;
                    ret = durchfuehrungsDates[anz - 1];
                }
                return ret;
            }
        }

        public bool HasNames {
            get {
                foreach (Name na in mySheet.Names) {
                    if (na.Name.Contains("SummeBrutto")) {
                        return true;
                    }
                }
                foreach (Name na in mySheet.Parent.Names) {
                    if (na.Name.Contains("SummeBrutto")) {
                        return true;
                    }
                }
                return false;
            }
        }

        public bool IsCalcSheet {
            get {
                Range r1 = mySheet.Cells[1, 1];
                Range r2 = mySheet.Cells[1, 3];
                Range r3 = mySheet.Cells[2, 3];
                string t1 = "";
                string t2 = "";
                string t3 = "";
                if (r1.Value != null) {
                    t1 = Convert.ToString(r1.Value);
                } else {
                    t1 = "";
                }
                if (r2.Value != null) {
                    t2 = Convert.ToString(r2.Value);
                } else {
                    t2 = "";
                }
                if (r3.Value != null) {
                    t3 = Convert.ToString(r3.Value);
                } else {
                    t3 = "";
                }
                if (((t1 == "Ka") | (t2 == "Kunde")) & (t3 != "Name")) {
                    return true;
                }
                return false;
            }
        }

        public bool IsReparatur {
            get {
                return this.reparatur;
            }
            set {
                this.reparatur = value;
            }
        }

        public double Kilometer {
            get {
                Range r1 = mySheet.Range[Settings.Default.ZelleKilometer];
                if (Extensions.isNumeric(Convert.ToString(r1.Value), System.Globalization.NumberStyles.Any)) {
                    return Extensions.Nz(r1.Value, 0);
                }
                return 0;
            }
        }

        public double KilometerDifferenz {
            get {
                return (Kilometer - KilometerNachPlz);
            }
        }

        public double KilometerNachPlz {
            get {
                Range r1 = mySheet.Range[Settings.Default.ZelleKmPlz];
                if (r1.Value == null) {
                    return Kilometer;
                }
                if (Extensions.isNumeric(Convert.ToString(r1.Value), System.Globalization.NumberStyles.Any)) {
                    return Extensions.Nz(r1.Value, 0) / 2;
                }
                return 0;
            }
        }

        public double Kundengruppe {
            get {
                Range r1 = mySheet.Range[Settings.Default.ZelleKundengruppe];
                if (Extensions.isNumeric(Convert.ToString(r1.Value), System.Globalization.NumberStyles.Any)) {
                    return Extensions.Nz(r1.Value, 0);
                }
                return 0;
            }
        }

        public string Kundenname {
            get {
                string ret = "";
                Range r1 = mySheet.Range[Settings.Default.ZelleKundenname];
                if (r1.Value != null) {
                    ret = Convert.ToString(r1.Value);
                }
                return ret;
            }
        }

        public DateTime LsDate1 {
            get {
                DateTime ret = new DateTime(1900, 1, 1);
                if (lieferscheinDates.Count > 0) {
                    lieferscheinDates.Sort();
                    int anz = lieferscheinDates.Count;
                    int row = (anz < 3 ? anz : 3);
                    ret = lieferscheinDates[0 + anz - row];
                }
                return ret;
            }
        }

        public DateTime LsDate2 {
            get {
                DateTime ret = new DateTime(1900, 1, 1);
                if (lieferscheinDates.Count > 1) {
                    lieferscheinDates.Sort();
                    int anz = lieferscheinDates.Count;
                    int row = (anz < 3 ? anz : 3);
                    ret = lieferscheinDates[1 + anz - row];
                }
                return ret;
            }
        }

        public DateTime LsDate3 {
            get {
                DateTime ret = new DateTime(1900, 1, 1);
                if (lieferscheinDates.Count > 2) {
                    lieferscheinDates.Sort();
                    int anz = lieferscheinDates.Count;
                    int row = (anz < 3 ? anz : 3);
                    ret = lieferscheinDates[2 + anz - row];
                }
                return ret;
            }
        }

        public List<ProjectRow> MyRows {
            get { return myRows; }
        }

        public Worksheet MySheet {
            get {
                return mySheet;
            }
        }

        public string Ort {
            get {
                string ret = "";
                Range r1 = mySheet.Range[Settings.Default.ZelleOrt];
                if (r1.Value != null) {
                    ret = Convert.ToString(r1.Value);
                }
                return ret;
            }
        }

        public double Plz {
            get {
                Range r1 = mySheet.Range[Settings.Default.ZellePlz];
                if (Extensions.isNumeric(Convert.ToString(r1.Value), System.Globalization.NumberStyles.Any)) {
                    return Extensions.Nz(r1.Value, 0);
                }
                return 0;
            }
        }

        public bool PlzExists {
            get {
                Range r1 = mySheet.Range[Settings.Default.ZellePlzExists];
                if (Extensions.isNumeric(Convert.ToString(r1.Value), System.Globalization.NumberStyles.Any)) {
                    return Extensions.Nz(r1.Value, 0) != 0;
                }
                return false;
            }
        }

        public string Projekt {
            get {
                string ret = "";
                Range r1 = mySheet.Range[Settings.Default.ZelleProjektbezeichnung];
                if (r1.Value != null) {
                    ret = Convert.ToString(r1.Value);
                }
                return ret;
            }
        }

        public int ProjektNummer {
            get {
                int proNr = 0;
                string proj = mySheet.Name.Substring(1);
                string n1 = Extensions.Left(proj, 2);
                if (Extensions.isNumeric(n1, System.Globalization.NumberStyles.Any)) {
                    proNr = Convert.ToInt32(n1);
                    return proNr;
                }
                n1 = Extensions.Left(proj, 1);
                if (Extensions.isNumeric(n1, System.Globalization.NumberStyles.Any)) {
                    proNr = Convert.ToInt32(n1);
                    return proNr;
                }
                return proNr;
            }
        }

        public string SheetName {
            get { return mySheet.Name; }
        }

        public int SheetType {
            get {
                string sheetType = mySheet.Name.Substring(1);
                foreach (CustomProperty p in mySheet.CustomProperties) {
                    if (p.Name == "SheetType") {
                        object ret = p.Value;
                        return Convert.ToInt32(p.Value);
                    }
                }
                if (sheetType.Contains("k")) {
                    return (int)Global.SheetType.Kalkulation;
                }
                if (sheetType.Contains("r")) {
                    return (int)Global.SheetType.Abrechnung; //***
                }
                if (sheetType.Contains("aa") | sheetType.Contains("ab") | sheetType.Contains("ac") | sheetType.Contains("ad") | sheetType.Contains("ae") | sheetType.Contains("af") | sheetType.Contains("ag")) {
                    return (int)Global.SheetType.Auftragsbestaetigung; //***
                }
                if (sheetType.Contains("b")) {
                    return (int)Global.SheetType.Projektbericht; //***
                }
                if (sheetType.Contains("l")) {
                    return (int)Global.SheetType.Lieferschein;
                }
                if (sheetType.Contains("f")) {
                    return (int)Global.SheetType.Rechnung; //***
                }
                if (sheetType.Contains("p")) {
                    return (int)Global.SheetType.Abnahmeprotokoll; //***
                }
                if (sheetType.Contains("ka") | sheetType.Contains("kb") | sheetType.Contains("kc") | sheetType.Contains("kd") | sheetType.Contains("ke") | sheetType.Contains("kf") | sheetType.Contains("kg")) {
                    return (int)Global.SheetType.Kalkulation;
                }
                if (sheetType.Contains("a")) {
                    return (int)Global.SheetType.Angebot; //***
                }
                return 0;
            }
        }

        public int SkontoFrist {
            get {
                if (!ExistsName("Skontofrist")) {
                    Range r1 = mySheet.Columns["AF"];
                    Range aCell = mySheet.Cells[301, "AF"];
                    object toFind = "Skonto netto";
                    int row = GetNextRowNumber(mySheet, r1, aCell, toFind, true);
                    if (row > 0) {
                        Range retBrutto = mySheet.Cells[row, "AN"];
                        if (retBrutto.Value != null) {
                            string t1 = Convert.ToString(retBrutto.Value);
                            if (Extensions.isNumeric(t1, System.Globalization.NumberStyles.Any)) {
                                return Convert.ToInt32(t1);
                            } else {
                                return 0;
                            }
                        }
                        return 0;
                    }
                    return 0;
                } else {
                    Range t1 = mySheet.Range["Skontofrist"];
                    return t1.Value;
                }
            }
        }

        public double SkontoBetrag {
            get {
                if (!ExistsName("ZusatzkostenSkontobrutto")) {
                    Range r1 = mySheet.Columns["AF"];
                    Range aCell = mySheet.Cells[301, "AF"];
                    object toFind = "Skonto netto";
                    int row = GetNextRowNumber(mySheet, r1, aCell, toFind, true);
                    if (row > 0) {
                        Range retBrutto = mySheet.Cells[row, "AR"];
                        return retBrutto.Value;
                    }
                    return 0.0;
                } else {
                    Range t1 = mySheet.Range["ZusatzkostenSkontobrutto"];
                    return t1.Value;
                }
            }
        }

        public double SkontoProzentSatz {
            get {
                if (!ExistsName("SkontoProzentsatz")) {
                    Range r1 = mySheet.Columns["AF"];
                    Range aCell = mySheet.Cells[301, "AF"];
                    object toFind = "Skonto netto";
                    int row = GetNextRowNumber(mySheet, r1, aCell, toFind, true);
                    if (row > 0) {
                        Range retBrutto = mySheet.Cells[row, "AM"];
                        return retBrutto.Value;
                    }
                    return 0.0;
                } else {
                    Range t1 = mySheet.Range["SkontoProzentsatz"];
                    return t1.Value;
                }
            }
        }

        public string Straße {
            get {
                string ret = "";
                Range r1 = mySheet.Range[Settings.Default.ZelleStrasse];
                if (r1.Value != null) {
                    ret = Convert.ToString(r1.Value);
                }
                return ret;
            }
        }

        public string Suchbegriff {
            get {
                string ret = Extensions.Left(Projekt, Projekt.IndexOf("p"));
                return ret;
            }
        }

        public double SummeBrutto {
            get {
                if (!ExistsName("SummeBrutto")) {
                    Range r1 = mySheet.Columns["AF"];
                    Range aCell = mySheet.Cells[301, "AF"];
                    object toFind = "Summe brutto";
                    int row = GetNextRowNumber(mySheet, r1, aCell, toFind, true);
                    if (row > 0) {
                        Range retBrutto = mySheet.Cells[row, "AR"];
                        return retBrutto.Value;
                    }
                    return 0.0;
                } else {
                    Range t1 = mySheet.Range["SummeBrutto"];
                    return t1.Value;
                }
            }
        }

        public double SummeNetto {
            get {
                if (HasNames) {
                    if (!ExistsName("SummeNetto")) {
                        Range r1 = mySheet.Columns["AF"];
                        Range aCell = mySheet.Cells[301, "AF"];
                        object toFind = "Summe netto";
                        int row = GetNextRowNumber(mySheet, r1, aCell, toFind, true);
                        if (row > 0) {
                            Range retNetto = mySheet.Cells[row, "AR"];
                            return retNetto.Value;
                        }
                        return 0.0;
                    }
                    Range t1 = mySheet.Range["SummeNetto"];
                    return t1.Value;

                }
                return 0.0;
            }
        }

        public string TempValue {
            get {
                string ret = "";
                Range r1 = mySheet.Range["A2"];
                if (r1.Value != null) {
                    ret = Convert.ToString(r1.Value);
                }
                return ret;
            }
        }

        public double Umsatzsteuer {
            get {
                if (HasNames) {
                    if (!ExistsName("Umsatzsteuer")) {
                        Range r1 = mySheet.Columns["AF"];
                        Range aCell = mySheet.Cells[301, "AF"];
                        object toFind = "Umsatzsteuer";
                        int row = GetNextRowNumber(mySheet, r1, aCell, toFind, true);
                        if (row > 0) {
                            Range retUst = mySheet.Cells[row, "AR"];
                            return retUst.Value;
                        }
                        return 0.0;
                    }
                    Range t1 = mySheet.Range["Umsatzsteuer"];
                    return t1.Value;

                }
                return 0.0;
            }
        }

        public double UmsatzsteuerProzent {
            get {
                if (!ExistsName("Umsatzsteuer")) {
                    Range r1 = mySheet.Columns["AF"];
                    Range aCell = mySheet.Cells[301, "AF"];
                    object toFind = "Umsatzsteuer";
                    int row = GetNextRowNumber(mySheet, r1, aCell, toFind, true);
                    if (row > 0) {
                        Range retUst = mySheet.Cells[row, "AM"];
                        return retUst.Value;
                    }
                    return 0.0;
                }
                Range t1 = mySheet.Range["Umsatzsteuer"];
                return t1.Value;
            }
        }

        public int ZahlungsFrist {
            get {
                if (!ExistsName("Zahlungsfrist")) {
                    Range r1 = mySheet.Columns["AF"];
                    Range aCell = mySheet.Cells[301, "AF"];
                    object toFind = "Skonto netto";
                    int row = GetNextRowNumber(mySheet, r1, aCell, toFind, true);
                    if (row > 0) {
                        Range retBrutto = mySheet.Cells[row, "AO"];
                        if (retBrutto.Value != null) {
                            string t1 = Convert.ToString(retBrutto.Value);
                            if (Extensions.isNumeric(t1, System.Globalization.NumberStyles.Any)) {
                                return Convert.ToInt32(t1);
                            } else {
                                return 0;
                            }
                        }
                    }
                    return 0;
                } else {
                    Range t1 = mySheet.Range["Skontofrist"];
                    return t1.Value;
                }
            }
        }

        public int ZaZeileSummeArbeitszeit {
            get;
            set;
        }

        public int ZaZeileSummeLeistungen {
            get;
            set;
        }

        public int ZaZeileSummeProjektLeistungen {
            get;
            set;
        }

        public int ZaZeileSummeInterneLeistungen {
            get;
            set;
        }

        public int ZeileAkonto {
            get;
            set;
        }

        public int ZeileDB {
            get;
            set;
        }

        public int ZeileSummeArbeitszeit {
            get;
            set;
        }

        public int ZeileSummeBrutto {
            get;
            set;
        }

        public int ZeileSummeGesamt {
            get;
            set;
        }

        public int ZeileSummeLeistungen {
            get;
            set;
        }

        public int ZeileSummeNetto {
            get;
            set;
        }

        #endregion

        #region Functions

        public bool ExistsName(string name) {
            foreach (Name n1 in mySheet.Names) {
                if (n1.Name.Contains(mySheet.Name) & n1.Name.Contains(name)) {
                    if (!n1.Value.Contains("#REF!")) {
                        return true;
                    }
                }
            }
            return false;
        }

        private int GetJeFreeRowNumber(Worksheet sh, Logger log) {
            int startRow = 3;
            int freeRow = 0;
            try {
                bool startRowSelect = false;
                while (startRowSelect == false & startRow < 5000) {
                    Range r1 = sh.Cells[startRow, 3];
                    if (r1.Value != null) {
                        if (!Extensions.isNumeric(r1.Value, System.Globalization.NumberStyles.Any)) {
                            if (((string)r1.Value).ToUpper().Contains("SUMME LEISTUNGEN")) {
                                freeRow = startRow;
                                startRowSelect = true;
                                sh.Activate();
                                Range row = sh.Rows[startRow - 1];
                                row.Select();
                                row.Copy();
                                row.Insert(Shift: XlInsertShiftDirection.xlShiftDown);
                            }
                        }
                    } else {
                        freeRow = startRow;
                        startRowSelect = true;
                    }
                    startRow++;
                }
            } catch (Exception ex) {
                log.Add("GetJeFreeRowNumber", ex);
            }
            return freeRow;
        }

        public Range GetNewRow(Worksheet sh, int startRow) {
            Range row = sh.Rows[startRow];
            return row;
        }

        private static int GetNextRowNumber(Worksheet sh, Range rCol, Range activeCell, object toFind) {
            return GetNextRowNumber(sh, rCol, activeCell, toFind, false);
        }

        private static int GetNextRowNumber(Worksheet sh, Range rCol, Range activeCell, object toFind, bool exact) {
            int ret = 0;
            Range r1;
            if (!exact) {
                r1 = rCol.Find(What: toFind, After: activeCell, SearchDirection: XlSearchDirection.xlNext, MatchCase: exact, LookAt: XlLookAt.xlPart);
            } else {
                r1 = rCol.Find(toFind, activeCell, SearchDirection: XlSearchDirection.xlNext, MatchCase: exact, LookAt: XlLookAt.xlWhole);
            }
            if (r1 != null) {
                ret = r1.Row;
            }
            return ret;
        }

        public Range GetRow(int rowNumber) {
            Range r1 = mySheet.Rows[rowNumber];
            return r1;
        }

        #endregion

        #region Methods

        private void AddAllSheetRows(int startRow, bool isCalcSheet, bool intern, bool onlyWithJobDate) {

            bool startRowSelect = false;
            if (!intern) {
                if (isCalcSheet) {
                    while (startRowSelect == false & startRow < 10000) {
                        Range row = mySheet.Rows[startRow];
                        ProjectRow r1 = new ProjectRow(row);
                        if (r1.RowType.ToUpper() == "DB") {
                            startRowSelect = true;
                        } else {
                            if ((onlyWithJobDate & r1.JobDatum != new DateTime(1900, 1, 1)) | (!onlyWithJobDate)) {
                                r1.RowNumber = startRow;
                                myRows.Add(r1);
                            }
                            startRow++;
                        }
                    }
                } else {
                    while (startRowSelect == false & startRow < 10000) {
                        Range row = mySheet.Rows[startRow];
                        ProjectRow r1 = new ProjectRow(row);
                        if (r1.KundenName == "") {
                            startRowSelect = true;
                        } else {
                            if ((onlyWithJobDate & r1.JobDatum != new DateTime(1900, 1, 1)) | (!onlyWithJobDate)) {
                                r1.RowNumber = startRow;
                                myRows.Add(r1);
                            }
                            startRow++;
                        }
                    }
                }
            } else {
                while (startRowSelect == false & startRow < 10000) {
                    Range row = mySheet.Rows[startRow];
                    ProjectRow r1 = new ProjectRow(row);
                    if (r1.Spalte("B").ToString().ToUpper().Contains("SUMME LEISTUNGEN")) {
                        startRowSelect = true;
                    } else {
                        if ((onlyWithJobDate & r1.JobDatum != new DateTime(1900, 1, 1)) | (!onlyWithJobDate)) {
                            r1.RowNumber = startRow;
                            myRows.Add(r1);
                        }
                        startRow++;
                    }

                }
            }
        }

        public void ActivateSheet() {
            mySheet.Activate();
        }

        public void CopyJob(string job, string subJob, Logger log) {
            Range rCol = this.mySheet.Columns["AF"];
            Range aCell = this.mySheet.Cells[301, "AF"];
            Range r1 = null;
            Range r2 = null;

            object toFind = job;
            int jobRow = GetNextRowNumber(this.mySheet, rCol, aCell, toFind, true);
            if (jobRow == 0) {
                log.Add(String.Format("Job {0}, {1} nicht gefunden.", job, subJob));
                return;
            }
            int firstRow = jobRow;

            bool weiter = false;
            try {
                while (weiter == false) {
                    r1 = this.mySheet.Rows[jobRow];
                    r2 = r1.Cells[1, "AG"];
                    if (r2.Value != null) {
                        string t1 = Convert.ToString(r2.Value);
                        if (t1 != subJob) {
                            aCell = mySheet.Cells[jobRow, "AF"];
                            jobRow = GetNextRowNumber(mySheet, rCol, aCell, toFind, true);
                            if (jobRow == firstRow) {
                                weiter = true;
                            }
                        } else {
                            Range c1 = this.mySheet.Rows[jobRow];
                            c1.Select();
                            c1.Copy();
                            c1.Insert(Shift: XlInsertShiftDirection.xlShiftDown);
                            Range c2 = this.mySheet.Rows[jobRow + 1];
                            ProjectRow jobCopyRow = new ProjectRow(c2);
                            jobCopyRow.Durchführender1 = "";
                            jobCopyRow.Durchführender2 = "";
                            jobCopyRow.ResetJobDatum();
                            jobCopyRow.JobOrt = "";
                            jobCopyRow.Hyperlink = "";
                            jobCopyRow.RechnungsNummer = 0;
                            jobCopyRow.SetFontBold(true);
                            weiter = true;
                        }
                    } else {
                        weiter = true;
                    }
                }
            } catch (Exception ex) {
                log.Add("CopyJob", ex);
                return;
            }
        }

        public void DeleteAllContent() {
            int startRow = 3;
            if (IsCalcSheet) {
                startRow = Settings.Default.ZeileStart;
            }
            Range r1 = mySheet.Rows[startRow.ToString() + ":" + "65000"];
            r1.ClearContents();
            r1.ClearComments();
        }

        public void DeleteAllRows() {
            foreach (ProjectRow row in MyRows) {
                myRows.Remove(row);
            }
        }

        private void SetRowNumbers() {
            if (IsCalcSheet) {

                bool weiter = false;
                int startRow = Settings.Default.ZeileStart;
                while (!weiter & startRow < 5000) {
                    Range r1 = mySheet.Cells[startRow, Settings.Default.SpalteSummenText];
                    string t1 = Extensions.Nz(Convert.ToString(r1.Value), "");
                    if (t1 != null) {
                        if (t1.ToUpper().Contains("GESAMT")) {
                            this.ZeileSummeGesamt = startRow;
                        }
                        if (t1.ToUpper().Contains("SUMME NETTO")) {
                            this.ZeileSummeNetto = startRow;
                        }
                        if (t1.ToUpper().Contains("SUMME BRUTTO")) {
                            this.ZeileSummeBrutto = startRow;
                        }
                        if (t1.ToUpper().Contains("AKONTO")) {
                            this.ZeileAkonto = startRow;
                        }
                        if (t1.ToUpper() == "DB") {
                            this.ZeileDB = startRow;
                            weiter = true;
                        }
                    }
                    startRow++;
                }
            } else {
                if (mySheet.Name.Contains("P") | mySheet.Name.Contains("M")) {
                    return;
                }
                if (((Workbook)mySheet.Parent).Name.Contains("le") | ((Workbook)mySheet.Parent).Name.Contains("pl") |
                    ((Workbook)mySheet.Parent).Name.Contains("il") | ((Workbook)mySheet.Parent).Name.Contains("je") | mySheet.Name.Contains("Leistungen")) {
                    bool weiter = false;
                    int startRow = Settings.Default.ZeileStartLeistungsDateien;
                    while (!weiter & startRow < 5000) {
                        Range r1 = mySheet.Cells[startRow, "B"];
                        Range r2 = mySheet.Cells[startRow, "C"];

                        string t1 = Extensions.Nz(Convert.ToString(r1.Value), "");
                        string t2 = t1 + Extensions.Nz(Convert.ToString(r2.Value), "");

                        if (t2 != "") {
                            if (t2.ToUpper().Contains("ARBEITSZEIT")) {
                                this.ZeileSummeArbeitszeit = startRow;
                            }
                            if (t2.ToUpper().Contains("LEISTUNGEN")) {
                                this.ZeileSummeLeistungen = startRow;
                                weiter = true;
                            }
                        }
                        startRow++;
                    }

                }
            }
        }

        public void HiddenConditionRows(Logger log) {
            if (IsCalcSheet) {
                try {
                    for (int i = this.ZeileSummeGesamt + 1; i < this.ZeileSummeNetto; i++) {
                        Range r1 = mySheet.Rows[i];
                        ProjectRow row = new ProjectRow(r1);
                        if (row.SummeVp == 0) {
                            r1.EntireRow.Hidden = true;
                        }
                    }
                } catch (Exception ex) {
                    log.Add(this.SheetName + "." + "HiddenConditionRows", ex);
                }
            }
        }

        public void HiddenSonderConditionRows(Logger log) {
            if (IsCalcSheet) {
                try {
                    for (int i = this.ZeileSummeBrutto + 1; i < this.ZeileAkonto; i++) {
                        Range r1 = mySheet.Rows[i];
                        ProjectRow row = new ProjectRow(r1);
                        if (row.SummeVp == 0) {
                            r1.EntireRow.Hidden = true;
                        }
                    }
                } catch (Exception ex) {
                    log.Add(this.SheetName + "." + "HiddenSonderConditionRows", ex);
                }
            }
        }

        private void LoadAuftragsDates() {
            if (!IsCalcSheet) {
                return;
            }
            DateTime ret = new DateTime(1900, 1, 1);
            Range rCol = mySheet.Columns["AF"];
            Range aCell = mySheet.Cells[301, "AF"];
            //aCell.Activate();
            object toFind = "Auftragsbestätigung";
            int rowLs1 = GetNextRowNumber(mySheet, rCol, aCell, toFind);
            int firstRow = rowLs1;
            while (rowLs1 != 0) {
                Range apDate = mySheet.Cells[rowLs1, "K"];
                if (apDate.Value != null) {
                    Range r2 = mySheet.Cells[rowLs1, "AG"];
                    if (r2.Value != null) {
                        string t1 = Convert.ToString(r2.Value);
                        if (t1.Contains("Verkauf")) {
                            auftragsBestaetigungsDatum = apDate.Value;
                        }
                    }
                }
                aCell = mySheet.Cells[rowLs1, "AF"];
                rowLs1 = GetNextRowNumber(mySheet, rCol, aCell, toFind);
                if (rowLs1 == firstRow) {
                    return;
                }
            }
        }

        private void LoadCheckReparatur() {
            if (!IsCalcSheet) {
                return;
            }


            Range rCol = mySheet.Columns["AF"];
            Range aCell = mySheet.Cells[301, "AF"];
            //aCell.Activate();
            object toFind = "Systemerfassung";
            int rowLs1 = GetNextRowNumber(mySheet, rCol, aCell, toFind);
            int firstRow = rowLs1;
            while (rowLs1 != 0) {
                Range r2 = mySheet.Cells[rowLs1, "AG"];
                if (r2.Value != null) {
                    string t1 = Convert.ToString(r2.Value);
                    if (t1.Contains("Reparatur")) {
                        reparatur = true;
                        return;
                    } else {
                        break;
                    }
                } else {
                    break;
                }
            }
            if (rowLs1 == 0) {
                return;
            }
            aCell = mySheet.Cells[rowLs1, "AF"];
            rowLs1 = GetNextRowNumber(mySheet, rCol, aCell, toFind);
            if (rowLs1 == firstRow) {
                return;
            }
        }

        private void LoadDurchführungsDates() {
            if (!IsCalcSheet) {
                return;
            }
            DateTime ret = new DateTime(1900, 1, 1);
            Range rCol = mySheet.Columns["AF"];
            Range aCell = mySheet.Cells[301, "AF"];
            //aCell.Activate();
            object toFind = "Logistik";
            int rowLs1 = GetNextRowNumber(mySheet, rCol, aCell, toFind, true);
            if (rowLs1 == 0) {
                rowLs1 = 301;
            }

            aCell = mySheet.Cells[rowLs1, "AF"];
            toFind = "Leistungen";
            int rowLs2 = GetNextRowNumber(mySheet, rCol, aCell, toFind, true);
            if (rowLs2 > rowLs1) {
                Range rDate;
                for (int i = rowLs1; i < rowLs2; i++) {
                    rDate = mySheet.Cells[i, "K"];
                    if (rDate.Value != null) {
                        if (!durchfuehrungsDates.Contains(rDate.Value)) {
                            durchfuehrungsDates.Add(rDate.Value);
                        }
                    }
                }
            }

        }

        private void LoadLieferscheinDates() {
            if (!IsCalcSheet) {
                return;
            }
            DateTime ret = new DateTime(1900, 1, 1);
            Range rCol = mySheet.Columns["AF"];
            Range aCell = mySheet.Cells[301, "AF"];
            object toFind = "Lieferschein";
            int rowLs1 = GetNextRowNumber(mySheet, rCol, aCell, toFind);
            toFind = "Leistungen";
            int maxRow = GetNextRowNumber(mySheet, rCol, aCell, toFind, true);
            if (maxRow == 0) {
                return;
            }
            int firstRow = rowLs1;
            while (rowLs1 != 0) {
                Range lsDate = mySheet.Cells[rowLs1, "K"];
                if (lsDate.Value != null) {
                    if (rowLs1 < maxRow) {
                        lieferscheinDates.Add(lsDate.Value);
                    }
                }
                aCell = mySheet.Cells[rowLs1, "AF"];
                toFind = "Lieferschein";
                rowLs1 = GetNextRowNumber(mySheet, rCol, aCell, toFind);
                if (rowLs1 == firstRow) {
                    return;
                }
            }
        }

        private void LoadLieferscheinDatesRep() {
            if (!IsCalcSheet) {
                return;
            }
            DateTime ret = new DateTime(1900, 1, 1);
            Range rCol = mySheet.Columns["AF"];
            Range aCell = mySheet.Cells[301, "AF"];
            object toFind = "Lieferschein Reparatur";
            int rowLs1 = GetNextRowNumber(mySheet, rCol, aCell, toFind);
            toFind = "Administration";
            int maxRow = GetNextRowNumber(mySheet, rCol, aCell, toFind, true);
            if (maxRow == 0) {
                return;
            }
            int firstRow = rowLs1;
            while (rowLs1 != 0) {
                if (LsDate1 != null) {
                    if (rowLs1 < maxRow) {
                        if (!lieferscheinDates.Contains(DurchfuehrungVon)) {
                            lieferscheinDates.Add(DurchfuehrungVon);
                        }
                    }
                }
                aCell = mySheet.Cells[rowLs1, "AF"];
                toFind = "Lieferschein Reparatur";
                rowLs1 = GetNextRowNumber(mySheet, rCol, aCell, toFind);
                if (rowLs1 == firstRow) {
                    return;
                }
            }
        }

        private void LoadAbnahmeprotokollDates() {
            if (!IsCalcSheet) {
                return;
            }
            DateTime ret = new DateTime(1900, 1, 1);
            Range rCol = mySheet.Columns["AF"];
            Range aCell = mySheet.Cells[301, "AF"];
            //aCell.Activate();
            object toFind = "Abnahmeprotokoll";
            int rowLs1 = GetNextRowNumber(mySheet, rCol, aCell, toFind);
            toFind = "Leistungen";
            int maxRow = GetNextRowNumber(mySheet, rCol, aCell, toFind, true);
            if (maxRow == 0) {
                return;
            }
            int firstRow = rowLs1;
            while (rowLs1 != 0) {
                Range apDate = mySheet.Cells[rowLs1, "K"];
                if (apDate.Value != null) {
                    if (rowLs1 < maxRow) {
                        abnahmeProtokollDates.Add(apDate.Value);
                    }
                }
                aCell = mySheet.Cells[rowLs1, "AF"];
                toFind = "Abnahmeprotokoll";
                rowLs1 = GetNextRowNumber(mySheet, rCol, aCell, toFind);
                if (rowLs1 == firstRow) {
                    return;
                }
            }
        }

        private void LoadAbnahmeprotokollDatesRep() {
            if (!IsCalcSheet) {
                return;
            }
            DateTime ret = new DateTime(1900, 1, 1);
            Range rCol = mySheet.Columns["AF"];
            Range aCell = mySheet.Cells[301, "AF"];
            //aCell.Activate();
            object toFind = "Abnahmeprotokoll  Reparatur";
            int rowLs1 = GetNextRowNumber(mySheet, rCol, aCell, toFind);
            toFind = "Administration";
            int maxRow = GetNextRowNumber(mySheet, rCol, aCell, toFind, true);
            if (maxRow == 0) {
                return;
            }
            int firstRow = rowLs1;
            while (rowLs1 != 0) {

                if (ApDate1 != null) {
                    if (rowLs1 < maxRow) {
                        if (!abnahmeProtokollDates.Contains(DurchfuehrungVon)) {
                            abnahmeProtokollDates.Add(DurchfuehrungVon);
                        }
                    }
                }
                aCell = mySheet.Cells[rowLs1, "AF"];
                toFind = "Abnahmeprotokoll  Reparatur";
                rowLs1 = GetNextRowNumber(mySheet, rCol, aCell, toFind);
                if (rowLs1 == firstRow) {
                    return;
                }
            }
        }

        public void Write(string bereich, Worksheet sourceSheet, GetFreeNumber getFreeNumber, bool withFormats, Logger log) {
            if (MyRows.Count != 0) {

                foreach (ProjectRow row in MyRows) {
                    int freeRowNumber = getFreeNumber(bereich, mySheet, log);
                    Range sourceRow = sourceSheet.Rows[row.RowNumber];
                    Range targetRow = GetNewRow(mySheet, freeRowNumber);

                    sourceSheet.Activate();
                    sourceRow.Select();
                    sourceRow.Copy();
                    mySheet.Activate();
                    targetRow.Select();
                    targetRow.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);
                    targetRow.PasteSpecial(XlPasteType.xlPasteComments, XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);
                    if (withFormats) {
                        targetRow.PasteSpecial(XlPasteType.xlPasteFormats, Operation: XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);
                    }

                }
            }
        }

        public void WriteJob(string job, string subJob, DateTime jobDate, string lf, string userName,
                int rechnungsNummer, string fileName, string display, Logger log) {

            Range rCol = this.mySheet.Columns["AF"];
            Range aCell = this.mySheet.Cells[301, "AF"];
            Range r1 = null;
            Range r2 = null;
            log.Add("Start WriteJob");
            object toFind = job;
            int jobRow = GetNextRowNumber(this.mySheet, rCol, aCell, toFind, true);
            if (jobRow == 0) {
                log.Add(String.Format("Job {0}, {1} nicht gefunden.", job, subJob));
                return;
            }
            int firstRow = jobRow;
            
            bool weiter = false;
            try {
                while (weiter == false) {
                    r1 = this.mySheet.Rows[jobRow];
                    r2 = r1.Cells[1, "AG"];
                    ProjectRow jobWriteRow = new ProjectRow(r1);
                    if (r2.Value != null ) {
                        string t1 = Convert.ToString(r2.Value);
                        if (t1 == subJob) {
                            log.Add("jobRow:" + jobRow.ToString());
                        }

                        if (t1 != subJob | jobWriteRow.JobDatum.Year > 1900 ) {
                            aCell = mySheet.Cells[jobRow, "AF"];
                            jobRow = GetNextRowNumber(mySheet, rCol, aCell, toFind, true);
                            //log.Add("jobRowS:" + jobRow.ToString());
                            if (jobRow == 0 | jobRow == firstRow) {
                                return;
                            }
                        } else {
                            weiter = true;
                        }
                    } else {
                        weiter = true;
                    }
                }

                ProjectRow myRow = new ProjectRow(r1);
                myRow.JobDatum = jobDate;
                //myRow.Leistung = job;
                myRow.Durchführender1 = userName;
                myRow.JobOrt = lf;

                if (rechnungsNummer != 0) {
                    myRow.RechnungsNummer = rechnungsNummer;
                }

                if (!String.IsNullOrEmpty(fileName)) {
                    Range r3 = r1.Cells[1, "J"];
                    r3.Hyperlinks.Add(r3, Address: fileName, TextToDisplay: display);
                }

                myRow.SetFontBold(false);

            } catch (Exception ex) {
                log.Add("WriteJob(" + jobRow.ToString() + ")", ex);
            }
        }

        #endregion

    }
}
